-- @owner: @peilinqian
-- @date: 2024/10/22
-- @testpoint: 创建before触发时机，update、delete触发事件的触发器，使用show triggers查询

@conn PrimaryDbBmode;

--step1：创建表 expect：成功
create table t_compatibility_grammar_show_case0007(id1 int, id2 int, id3 int);
create table t_compatibility_grammar_show_case0007_1(id1 int, id2 int, id3 int);
insert into t_compatibility_grammar_show_case0007 values(100,200,300);
insert into t_compatibility_grammar_show_case0007 values(400,500,600);
insert into t_compatibility_grammar_show_case0007 values(700,800,900);
insert into t_compatibility_grammar_show_case0007_1 values(100,2,3);
insert into t_compatibility_grammar_show_case0007_1 values(400,5,6);
insert into t_compatibility_grammar_show_case0007_1 values(700,8,9);

--step2：创建触发器函数 expect：成功
create or replace function f_compatibility_grammar_show_case0007() returns trigger as
$$
declare
begin
 update t_compatibility_grammar_show_case0007_1 set id3=new.id3 where id1 = old.id1;
 delete from t_compatibility_grammar_show_case0007_1 where id1 = old.id1;
 return old;
end
$$ language plpgsql;
/

--step3：创建after insert类型触发器 expect：成功
create trigger trigger_compatibility_grammar_show_case0007 before update or delete on t_compatibility_grammar_show_case0007 for each row execute procedure f_compatibility_grammar_show_case0007();
/

--step4：执行触发事件 expect：成功
----执行update触发事件并检查触发结果
update t_compatibility_grammar_show_case0007 set id1=1 where id3<800;
select * from t_compatibility_grammar_show_case0007;
select * from t_compatibility_grammar_show_case0007_1;
----执行delete触发事件并检查触发结果
delete from t_compatibility_grammar_show_case0007 where id3 < 800;
select * from t_compatibility_grammar_show_case0007;
select * from t_compatibility_grammar_show_case0007_1;


--step5：查询触发器信息不加可选项 expect：显示结果正确
show triggers ;

--step6：查询触发器信息from schema； expect：显示结果正确
show triggers from public;

--step7：查询触发器信息添加where条件； expect：显示结果正确
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0007';
show triggers from public where "Trigger" like 'trigger_compatibility_grammar_show_case0007';
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0007' and "Event"='UPDATE' or "Event"='DELETE' ;
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0007' and "Timing"='BEFORE';

--step8：清理数据； expect：显示结果正确
drop trigger trigger_compatibility_grammar_show_case0007 on t_compatibility_grammar_show_case0007;
drop function f_compatibility_grammar_show_case0007();
drop table t_compatibility_grammar_show_case0007;
drop table t_compatibility_grammar_show_case0007_1;
show triggers from public where "Trigger"='trigger_compatibility_grammar_show_case0007';